﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using EntityObjects;

namespace DAL_V1.App_Code.DBBroker
{
    public class PurchaseOrderDBBroker
    {
        private SSISDataSet ds;
        private SSISDataSetTableAdapters.PurchaseOrderTableAdapter tda;
        private SSISDataSetTableAdapters.PurchaseOrderDetailsTableAdapter detailstda;
        private int index;
        private List<PurchaseOrder> po_list;
        private List<PurchaseOrderDetails> poDetails_list;
        private PurchaseOrder po;
        private PurchaseOrderDetails poDetails;
        private SSISDataSet.PurchaseOrderDataTable po_td;
        private SSISDataSet.PurchaseOrderDetailsDataTable poDetails_td;

        //singleton
        private static bool instance_flag = false;
        private static PurchaseOrderDBBroker instance;

        private PurchaseOrderDBBroker()
        {
            ds = new SSISDataSet();
            tda = new SSISDataSetTableAdapters.PurchaseOrderTableAdapter();
            detailstda = new SSISDataSetTableAdapters.PurchaseOrderDetailsTableAdapter();

            tda.Fill(ds.PurchaseOrder);
            detailstda.Fill(ds.PurchaseOrderDetails);
        }

        public static PurchaseOrderDBBroker getPurchaseOrderDBBroker()
        {
            if (!instance_flag)
            {
                instance = new PurchaseOrderDBBroker();
                instance_flag = true;

                return instance;
            }
            else
            {
                return instance;
            }
        }


        public List<PurchaseOrder> selectAllPurchaseOrder()
        {
            po_list = new List<PurchaseOrder>();
            for (int i = 0; i < ds.PurchaseOrder.Rows.Count; i++)
            {
                po = new PurchaseOrder();
                po.PONo = ds.PurchaseOrder[i].PONo;
                po.SupplierId = ds.PurchaseOrder[i].SupplierId;
                po.OrderedOn = ds.PurchaseOrder[i].OrderedOn;
                po.WantedDate = ds.PurchaseOrder[i].WantedDate;
                po.OrderedBy = ds.PurchaseOrder[i].OrderedBy;
                po.Status = ds.PurchaseOrder[i].Status;

                //Details
                poDetails_list = selectPODetailsByPOId(ds.PurchaseOrder[i].PONo);
                po.PoDetails_list = poDetails_list;

                po_list.Add(po);
            }
            return po_list;
        }

        public DataTable selectAllPurchaseOrder_DT()
        {
            return ds.PurchaseOrder;
        }

        public int addPurchaseOrder(PurchaseOrder po)
        {
            SSISDataSet.PurchaseOrderRow newRow = ds.PurchaseOrder.NewPurchaseOrderRow();
            newRow.PONo = po.PONo;
            newRow.SupplierId = po.SupplierId;
            newRow.OrderedBy = po.OrderedBy;
            newRow.OrderedOn = po.OrderedOn;
            newRow.WantedDate = po.WantedDate;
            newRow.Status = po.Status;
                     
            ds.PurchaseOrder.Rows.Add(newRow);
            return tda.Update(ds.PurchaseOrder);
        }

        //private int getIndex(String Id)
        //{
        //    SSISDataSet.PurchaseOrderRow row = ds.PurchaseOrder.FindByPONo(Id);
        //    return ds.PurchaseOrder.Rows.IndexOf(row);
        //}

        private SSISDataSet.PurchaseOrderRow getRowById(String Id)
        {
            return ds.PurchaseOrder.FindByPONo(Id);
        }

        public int updatePurchaseOrder(PurchaseOrder po, String Id)
        {
            SSISDataSet.PurchaseOrderRow row = getRowById(Id);
            if (row != null)
            {
                row.PONo = po.PONo;
                row.SupplierId = po.SupplierId;
                row.OrderedBy = po.OrderedBy;
                row.OrderedOn = po.OrderedOn;
                row.WantedDate = po.WantedDate;
                row.Status = po.Status;
            }
        
            return tda.Update(ds.PurchaseOrder);
        }

        public int deletePurchaseOrder(String Id)
        {
            SSISDataSet.PurchaseOrderRow row = getRowById(Id);
            if (row != null)
            {
                ds.PurchaseOrder.Rows.Remove(row);
            }
            return tda.Update(ds.PurchaseOrder);
        }

        public PurchaseOrder selectPurchaseOrder(String Id)
        {
            SSISDataSet.PurchaseOrderRow r = getRowById(Id);
            if (r != null)
            {
                po = new PurchaseOrder();
                po.PONo = r.PONo;
                po.SupplierId = r.SupplierId;
                po.OrderedOn = r.OrderedOn;
                po.WantedDate = r.WantedDate;
                po.OrderedBy = r.OrderedBy;
                po.Status = r.Status;

                //Details
                poDetails_list = selectPODetailsByPOId(r.PONo);
                po.PoDetails_list = poDetails_list;

                return po;
            }
            else
            {
                return null;
            }
        }

        //Purchase Order Details
        public List<PurchaseOrderDetails> selectAllPurchaseOrderDetails()
        {
            poDetails_list = new List<PurchaseOrderDetails>();
            for (int i = 0; i < ds.PurchaseOrderDetails.Rows.Count; i++)
            {
                poDetails = new PurchaseOrderDetails();
                poDetails.PONo = ds.PurchaseOrderDetails[i].PONo;
                poDetails.ItemId = ds.PurchaseOrderDetails[i].ItemId;
                poDetails.OrderQty = ds.PurchaseOrderDetails[i].OrderQty;
                poDetails.Price = Convert.ToDouble(ds.PurchaseOrderDetails[i].Price);
                poDetails.Amount = Convert.ToDouble(ds.PurchaseOrderDetails[i].Amount);
                poDetails.DeliveredQty = ds.PurchaseOrderDetails[i].DeliveredQty;

                poDetails_list.Add(poDetails);
            }
            return poDetails_list;
        }

        public DataTable selectAllPurchaseOrderDetails_DT()
        {
            return ds.PurchaseOrderDetails;
        }

        public int addPurchaseOrderDetails(PurchaseOrderDetails poDetails)
        {
            SSISDataSet.PurchaseOrderDetailsRow newRow = ds.PurchaseOrderDetails.NewPurchaseOrderDetailsRow();
            newRow.PONo = poDetails.PONo;
            newRow.ItemId = poDetails.ItemId;
            newRow.OrderQty = poDetails.OrderQty;
            newRow.Price = Convert.ToDecimal(poDetails.Price);
            newRow.Amount = Convert.ToDecimal(poDetails.Amount);
            newRow.DeliveredQty = poDetails.DeliveredQty;

            ds.PurchaseOrderDetails.Rows.Add(newRow);
            return detailstda.Update(ds.PurchaseOrderDetails);
        }

        //private int getIndex(String poNo,String itemId)
        //{
        //    SSISDataSet.PurchaseOrderDetailsRow row = ds.PurchaseOrderDetails.FindByPONoItemId(poNo, itemId);
        //    return ds.PurchaseOrderDetails.Rows.IndexOf(row);
        //}

        private SSISDataSet.PurchaseOrderDetailsRow getRowById(String poNo, String itemId)
        {
            return ds.PurchaseOrderDetails.FindByPONoItemId(poNo, itemId);
        }

        public int updatePurchaseOrderDetails(PurchaseOrderDetails poDetails, String poNo, String itemId)
        {
            SSISDataSet.PurchaseOrderDetailsRow row = getRowById(poNo, itemId);
            if (row != null)
            {
                row.OrderQty = poDetails.OrderQty;
                row.Price = Convert.ToDecimal(poDetails.Price);
                row.Amount = Convert.ToDecimal(poDetails.Amount);
                row.DeliveredQty = poDetails.DeliveredQty;
            }

            return detailstda.Update(ds.PurchaseOrderDetails);
        }

        public int deletePurchaseOrderDetails(String poNo,String itemId)
        {
            SSISDataSet.PurchaseOrderDetailsRow row = getRowById(poNo,itemId);
            if (row != null)
            {
                ds.PurchaseOrderDetails.Rows.Remove(row);
            }
            return detailstda.Update(ds.PurchaseOrderDetails);
        }

        public PurchaseOrderDetails selectPurchaseOrderDetails(String poNo,String itemId)
        {
            SSISDataSet.PurchaseOrderDetailsRow r = getRowById(poNo, itemId);
            if (r != null)
            {
                poDetails = new PurchaseOrderDetails();
                poDetails.PONo = r.PONo;
                poDetails.ItemId = r.ItemId;
                poDetails.OrderQty = r.OrderQty;
                poDetails.Price = Convert.ToDouble(r.Price);
                poDetails.Amount = Convert.ToDouble(r.Amount);
                poDetails.DeliveredQty = r.DeliveredQty;

                return poDetails;
            }
            else
            {
                return null;
            }
        }

        //select by poId
        public List<PurchaseOrderDetails> selectPODetailsByPOId(String id)
        {
            poDetails_td = detailstda.GetDataByPOId(id);

            if (poDetails_td != null)
            {
                poDetails_list = new List<PurchaseOrderDetails>();
                for (int i = 0; i < poDetails_td.Rows.Count; i++)
                {
                    poDetails = new PurchaseOrderDetails();
                    poDetails.PONo = poDetails_td[i].PONo;
                    poDetails.ItemId = poDetails_td[i].ItemId;
                    poDetails.OrderQty = poDetails_td[i].OrderQty;
                    poDetails.Price = Convert.ToDouble(poDetails_td[i].Price);
                    poDetails.Amount = Convert.ToDouble(poDetails_td[i].Amount);
                    poDetails.DeliveredQty = poDetails_td[i].DeliveredQty;

                    poDetails_list.Add(poDetails);
                }
                return poDetails_list;
            }
            else
            {
                return null;
            }
        }
    }
}
